#include "usermanagement.h"
#include "core/databasepool/databasepool.h"
#include <QSqlQuery>
#include <QVariant>
#include <QSqlError>
#include <QDebug>
#include <QCryptographicHash>

// 获取最后一次操作的错误信息
QString UserManagement::getLastError() const {
    return m_lastError;
}

// 创建用户表
void UserManagement::createUserTable() {
    QMutexLocker locker(&m_mutex);
    QSqlDatabase db = DatabasePool::instance().getConnection();
    QSqlQuery query(db);
    // 执行创建用户表的SQL语句
    if (!query.exec(R"(
                    CREATE TABLE IF NOT EXISTS users (
                    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT UNIQUE NOT NULL,
                    password TEXT NOT NULL,
                    email TEXT UNIQUE,
                    phone TEXT UNIQUE,
                    role TEXT NOT NULL,
                    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S', 'now', '+08:00')),
                    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S', 'now', '+08:00'))
                    )
                    )")) {
        m_lastError = query.lastError().text();
        qDebug() << "users创建表失败: " << m_lastError;
    } else {
        // 检查管理员账号是否存在
        QSqlQuery checkQuery(db);
        checkQuery.prepare("SELECT COUNT(*) FROM users WHERE username = :username");
        checkQuery.bindValue(":username", "admin");
        if (checkQuery.exec() && checkQuery.next()) {
            int count = checkQuery.value(0).toInt();
            if (count == 0) {
                // 对密码进行哈希处理
                QString password = "admin";
                QString hashedPassword = QString(QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha256).toHex());
                QSqlQuery insertQuery(db);
                // 插入账号、密码和角色
                insertQuery.prepare("INSERT INTO users (username, password, role) VALUES (:username, :password, :role)");
                insertQuery.bindValue(":username", "admin");
                insertQuery.bindValue(":password", hashedPassword);
                insertQuery.bindValue(":role", "admin");
                if (insertQuery.exec()) {
                    qDebug() << "管理员账号创建成功";
                } else {
                    m_lastError = insertQuery.lastError().text();
                    qDebug() << "插入管理员账号失败: " << m_lastError;
                }
            }
        } else {
            m_lastError = checkQuery.lastError().text();
            qDebug() << "检查管理员账号是否存在时出错: " << m_lastError;
        }
    }
    DatabasePool::instance().releaseConnection(db);
}

// 构造函数
UserManagement::UserManagement(QObject *parent) : QObject(parent) {
    createUserTable();
}

// 析构函数
UserManagement::~UserManagement() {
    // 无需在这里释放连接，因为连接在每个操作完成后已释放
}

// 用户登录
bool UserManagement::login(const QString &username, const QString &password) {
    QMutexLocker locker(&m_mutex);
    QSqlDatabase db = DatabasePool::instance().getConnection();
    QSqlQuery query(db);
    // 准备查询用户密码的SQL语句
    query.prepare("SELECT password FROM users WHERE username = :username");
    query.bindValue(":username", username);
    if (query.exec() && query.next()) {
        // 获取数据库中存储的密码
        QString hashedPassword = query.value(0).toString();
        // 对输入的密码进行哈希处理
        QString inputHashedPassword = QString(QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha256).toHex());
        // 比较密码是否匹配
        if (hashedPassword == inputHashedPassword) {
            DatabasePool::instance().releaseConnection(db);
            return true;
        } else {
            m_lastError = "密码错误";
        }
    } else {
        m_lastError = "用户名不存在";
    }
    DatabasePool::instance().releaseConnection(db);
    return false;
}

// 用户注册
bool UserManagement::registerUser(const QString &username, const QString &password, const QString &email, const QString &phone) {
    QMutexLocker locker(&m_mutex);
    QSqlDatabase db = DatabasePool::instance().getConnection();
    // 对输入的密码进行哈希处理
    QString hashedPassword = QString(QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha256).toHex());

    // 检查用户名是否已存在
    QSqlQuery checkUsernameQuery(db);
    checkUsernameQuery.prepare("SELECT COUNT(*) FROM users WHERE username = :username");
    checkUsernameQuery.bindValue(":username", username);
    if (checkUsernameQuery.exec() && checkUsernameQuery.next() && checkUsernameQuery.value(0).toInt() > 0) {
        m_lastError = "用户名已存在";
        DatabasePool::instance().releaseConnection(db);
        return false;
    }

    // 检查邮箱是否已存在
    QSqlQuery checkEmailQuery(db);
    checkEmailQuery.prepare("SELECT COUNT(*) FROM users WHERE email = :email");
    checkEmailQuery.bindValue(":email", email);
    if (checkEmailQuery.exec() && checkEmailQuery.next() && checkEmailQuery.value(0).toInt() > 0) {
        m_lastError = "该邮箱已注册";
        DatabasePool::instance().releaseConnection(db);
        return false;
    }

    // 检查手机号是否已存在
    QSqlQuery checkPhoneQuery(db);
    checkPhoneQuery.prepare("SELECT COUNT(*) FROM users WHERE phone = :phone");
    checkPhoneQuery.bindValue(":phone", phone);
    if (checkPhoneQuery.exec() && checkPhoneQuery.next() && checkPhoneQuery.value(0).toInt() > 0) {
        m_lastError = "该手机号已注册";
        DatabasePool::instance().releaseConnection(db);
        return false;
    }

    QSqlQuery insertQuery(db);
    // 准备插入用户信息的SQL语句
    insertQuery.prepare("INSERT INTO users (username, password, email, phone, role) VALUES (:username, :password, :email, :phone, 'user')");
    insertQuery.bindValue(":username", username);
    insertQuery.bindValue(":password", hashedPassword);
    insertQuery.bindValue(":email", email);
    insertQuery.bindValue(":phone", phone);

    // 执行插入操作
    if (insertQuery.exec()) {
        DatabasePool::instance().releaseConnection(db);
        return true;
    } else {
        m_lastError = insertQuery.lastError().text();
        DatabasePool::instance().releaseConnection(db);
        return false;
    }
}

// 根据用户名、手机或邮箱查询用户信息
QMap<QString, QVariant> UserManagement::queryUserByUsernamePhoneMail(const QString &username, const QString &phone, const QString &email) {
    QMutexLocker locker(&m_mutex);
    QSqlDatabase db = DatabasePool::instance().getConnection();
    QMap<QString, QVariant> userInfo;
    QSqlQuery query(db);
    // 修改查询语句，包含 created_at 和 updated_at 字段
    QString sql = "SELECT user_id, username, password, email, phone, role, created_at, updated_at FROM users WHERE ";
    QMap<QString, QVariant> bindings;

    if (!username.isEmpty()) {
        sql += "username = :username";
        bindings[":username"] = username;
    } else if (!phone.isEmpty()) {
        sql += "phone = :phone";
        bindings[":phone"] = phone;
    } else if (!email.isEmpty()) {
        sql += "email = :email";
        bindings[":email"] = email;
    } else {
        m_lastError = "至少需要提供一个查询参数";
        DatabasePool::instance().releaseConnection(db);
        return userInfo;
    }

    query.prepare(sql);
    for (auto it = bindings.begin(); it != bindings.end(); ++it) {
        query.bindValue(it.key(), it.value());
    }

    if (query.exec() && query.next()) {
        userInfo["user_id"] = query.value("user_id");
        userInfo["username"] = query.value("username");
        userInfo["password"] = query.value("password");
        userInfo["email"] = query.value("email");
        userInfo["phone"] = query.value("phone");
        userInfo["role"] = query.value("role");
        userInfo["created_at"] = query.value("created_at");
        userInfo["updated_at"] = query.value("updated_at");
    } else {
        m_lastError = "未找到匹配的用户信息";
    }
    DatabasePool::instance().releaseConnection(db);
    return userInfo;
}

// 根据用户名修改用户信息
bool UserManagement::updateByUsername(const QString &oldUsername, const QString &newUsername, const QString &password, const QString &email, const QString &phone, const QString &role) {
    QMutexLocker locker(&m_mutex);
    QSqlDatabase db = DatabasePool::instance().getConnection();
    QString updateQuery = "UPDATE users SET ";
    QMap<QString, QVariant> bindings;
    bool hasUpdate = false;

    // 处理用户名更新
    if (!newUsername.isEmpty()) {
        QSqlQuery checkUsernameQuery(db);
        checkUsernameQuery.prepare("SELECT COUNT(*) FROM users WHERE username = :newUsername AND username != :oldUsername");
        checkUsernameQuery.bindValue(":newUsername", newUsername);
        checkUsernameQuery.bindValue(":oldUsername", oldUsername);
        if (checkUsernameQuery.exec() && checkUsernameQuery.next() && checkUsernameQuery.value(0).toInt() > 0) {
            m_lastError = "该用户名已注册";
            DatabasePool::instance().releaseConnection(db);
            return false;
        }
        updateQuery += "username = :newUsername";
        bindings[":newUsername"] = newUsername;
        hasUpdate = true;
    }

    // 处理密码更新
    if (!password.isEmpty()) {
        if (hasUpdate) updateQuery += ", ";
        QString hashedPassword = QString(QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha256).toHex());
        updateQuery += "password = :password";
        bindings[":password"] = hashedPassword;
        hasUpdate = true;
    }

    // 处理邮箱更新
    if (!email.isEmpty()) {
        QSqlQuery checkEmailQuery(db);
        checkEmailQuery.prepare("SELECT COUNT(*) FROM users WHERE email = :email AND username != :oldUsername");
        checkEmailQuery.bindValue(":email", email);
        checkEmailQuery.bindValue(":oldUsername", oldUsername);
        if (checkEmailQuery.exec() && checkEmailQuery.next() && checkEmailQuery.value(0).toInt() > 0) {
            m_lastError = "该邮箱已注册";
            DatabasePool::instance().releaseConnection(db);
            return false;
        }
        if (hasUpdate) updateQuery += ", ";
        updateQuery += "email = :email";
        bindings[":email"] = email;
        hasUpdate = true;
    }

    // 处理手机号更新
    if (!phone.isEmpty()) {
        QSqlQuery checkPhoneQuery(db);
        checkPhoneQuery.prepare("SELECT COUNT(*) FROM users WHERE phone = :phone AND username != :oldUsername");
        checkPhoneQuery.bindValue(":phone", phone);
        checkPhoneQuery.bindValue(":oldUsername", oldUsername);
        if (checkPhoneQuery.exec() && checkPhoneQuery.next() && checkPhoneQuery.value(0).toInt() > 0) {
            m_lastError = "该手机号已注册";
            DatabasePool::instance().releaseConnection(db);
            return false;
        }
        if (hasUpdate) updateQuery += ", ";
        updateQuery += "phone = :phone";
        bindings[":phone"] = phone;
        hasUpdate = true;
    }

    // 处理角色更新（无需唯一性检查）
    if (!role.isEmpty()) {
        if (hasUpdate) updateQuery += ", ";
        updateQuery += "role = :role";
        bindings[":role"] = role;
        hasUpdate = true;
    }

    // 更新 updated_at 字段
    if (hasUpdate) {
        updateQuery += ", updated_at = strftime('%Y-%m-%d %H:%M:%S', 'now', '+08:00')";
    }

    if (!hasUpdate) {
        DatabasePool::instance().releaseConnection(db);
        return true; // 无更新内容
    }

    updateQuery += " WHERE username = :oldUsername";
    bindings[":oldUsername"] = oldUsername;

    QSqlQuery query(db);
    query.prepare(updateQuery);
    for (auto it = bindings.begin(); it != bindings.end(); ++it) {
        query.bindValue(it.key(), it.value());
    }

    if (query.exec()) {
        DatabasePool::instance().releaseConnection(db);
        return true;
    } else {
        m_lastError = query.lastError().text();
        DatabasePool::instance().releaseConnection(db);
        return false;
    }
}

// 根据用户名删除用户
bool UserManagement::deleteUserByUsername(const QString &username) {
    QMutexLocker locker(&m_mutex);
    QSqlDatabase db = DatabasePool::instance().getConnection();
    QSqlQuery query(db);
    query.prepare("DELETE FROM users WHERE username = ?");
    query.addBindValue(username);

    if (query.exec()) {
        DatabasePool::instance().releaseConnection(db);
        return true;
    } else {
        m_lastError = query.lastError().text();
        DatabasePool::instance().releaseConnection(db);
        return false;
    }
}
